﻿
namespace SistemaLogistica.DataAccess
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using SistemaLogistica.Business.Entities;

    public class GuiaRemisionData : BaseDataAccess
    {
        private GuiaRemisionData() { }

        private readonly static GuiaRemisionData instancia = new GuiaRemisionData();

        public static GuiaRemisionData Instancia
        {
            get { return instancia; }
        }

        public List<GuiaRemision> Listar()
        {
            List<GuiaRemision> listaGuiaRemision = new List<GuiaRemision>();
            NotaIngreso  notaIngresoData = new  NotaIngreso();

            try
            {
                DbCommand command = db.GetStoredProcCommand("GetAllFromGuiaRemision");
                IDataReader dr = db.ExecuteReader(command);
                while (dr.Read())
                {
                    GuiaRemision  guiaRemision = new GuiaRemision
                    {
                        IdGuiaRemision = dr.GetInt32(dr.GetOrdinal("IdGuiaRemision")),
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        Origen = dr.GetString(dr.GetOrdinal("Origen")),
                        Destino = dr.GetString(dr.GetOrdinal("Destino")),
                        Transporte = dr.GetString(dr.GetOrdinal("Transporte")),
                        IdDocumento = dr.GetInt32(dr.GetOrdinal("IdDocumento")),
                        TipoGuia = dr.GetInt32(dr.GetOrdinal("TipoGuia")),
                        Motivo= dr.GetString(dr.GetOrdinal("Motivo")),
                        FechaTranslado = dr.GetDateTime(dr.GetOrdinal("FechaTranslado")),
                        Fecha = dr.GetDateTime(dr.GetOrdinal("FechaEmision")),
                        Empleados = EmpleadoData.Instancia.ObtenerPorID(dr.GetInt16(dr.GetOrdinal("IDEmpleado"))),
                        Detalle = DetalleGuiaRemisionData.Instancia.ListarPorGuiaRemision(dr.GetInt32(dr.GetOrdinal("IdGuiaRemision")))
                    };
                    listaGuiaRemision.Add(guiaRemision);

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return listaGuiaRemision;
        }

        public List<GuiaRemision> ListarPorTipo(int tipo)
        {
            List<GuiaRemision> listaGuiaRemision = new List<GuiaRemision>();
            NotaIngreso notaIngresoData = new NotaIngreso();

            try
            {
                DbCommand command = db.GetStoredProcCommand("GetGuiaRemisionByTipo");
                db.AddInParameter(command, "tipo", DbType.Int32,tipo);
                IDataReader dr = db.ExecuteReader(command);
                while (dr.Read())
                {
                    GuiaRemision guiaRemision = new GuiaRemision
                    {
                        IdGuiaRemision = dr.GetInt32(dr.GetOrdinal("IdGuiaRemision")),
                        Descripcion = dr.GetString(dr.GetOrdinal("Descripcion")),
                        Origen = dr.GetString(dr.GetOrdinal("Origen")),
                        Destino = dr.GetString(dr.GetOrdinal("Destino")),
                        Transporte = dr.GetString(dr.GetOrdinal("Transporte")),
                        IdDocumento = dr.GetInt32(dr.GetOrdinal("IdDocumento")),
                        TipoGuia = dr.GetInt32(dr.GetOrdinal("TipoGuia")),
                        Motivo = dr.GetString(dr.GetOrdinal("Motivo")),
                        FechaTranslado = dr.GetDateTime(dr.GetOrdinal("FechaTranslado")),
                        Fecha = dr.GetDateTime(dr.GetOrdinal("FechaEmision")),
                        Empleados = EmpleadoData.Instancia.ObtenerPorID(dr.GetInt16(dr.GetOrdinal("IDEmpleado"))),
                        Detalle = DetalleGuiaRemisionData.Instancia.ListarPorGuiaRemision(dr.GetInt32(dr.GetOrdinal("IdGuiaRemision")))
                    };
                    listaGuiaRemision.Add(guiaRemision);

                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            return listaGuiaRemision;
        }

        public int Registrar(GuiaRemision guiaRemision)
        {
            int idGuiaRemision = 0;
            using (DbConnection conection = db.CreateConnection())
            {
                conection.Open();
                using (DbTransaction transaction = conection.BeginTransaction())
                {
                    try
                    {
                        DbCommand cmdGuiaRemision = db.GetStoredProcCommand("InsertGuiaRemision");
                        db.AddInParameter(cmdGuiaRemision,"@origen", DbType.String,guiaRemision.Origen);
                        db.AddInParameter(cmdGuiaRemision, "@destino", DbType.String,guiaRemision.Destino);
                        db.AddInParameter(cmdGuiaRemision, "@motivo", DbType.String,guiaRemision.Motivo);
                        db.AddInParameter(cmdGuiaRemision, "@transporte", DbType.String,guiaRemision.Transporte);
                        db.AddInParameter(cmdGuiaRemision, "@descripcion", DbType.String,guiaRemision.Descripcion);
                        db.AddInParameter(cmdGuiaRemision, "@idEmpleado", DbType.Int32,guiaRemision.Empleados.IdEmpleado);
                        db.AddInParameter(cmdGuiaRemision, "@fechaEmision", DbType.DateTime,guiaRemision.Fecha);
                        db.AddInParameter(cmdGuiaRemision, "@fechaTranslado", DbType.DateTime,guiaRemision.FechaTranslado);
                        db.AddInParameter(cmdGuiaRemision, "@idDocumento", DbType.Int32,guiaRemision.IdDocumento);
                        db.AddInParameter(cmdGuiaRemision, "@tipoGuia", DbType.Int32,guiaRemision.TipoGuia);
                        db.AddOutParameter(cmdGuiaRemision, "@idguiaRemision", DbType.Int32,10);
                        db.ExecuteNonQuery(cmdGuiaRemision, transaction);
                        idGuiaRemision = Convert.ToInt32(cmdGuiaRemision.Parameters["@idGuiaRemision"].Value);

                        DbCommand cmdDetalle = db.GetStoredProcCommand("InsertDetalleGuiaRemision");
                        db.AddInParameter(cmdDetalle,"@cantidad", DbType.Decimal);
                        db.AddInParameter(cmdDetalle,"@precio", DbType.Currency);
                        db.AddInParameter(cmdDetalle,"@idBien", DbType.Int32);
                        db.AddInParameter(cmdDetalle,"@idGuiaRemision", DbType.Int32);

                        foreach (DetalleGuiaRemision d in guiaRemision.Detalle)
                        {
                            db.SetParameterValue(cmdDetalle,"@cantidad",d.Cantidad);
                            db.SetParameterValue(cmdDetalle,"@precio",d.Precio);
                            db.SetParameterValue(cmdDetalle,"@idBien",d.Bien.IdBien);
                            db.SetParameterValue(cmdDetalle,"@idGuiaRemision",idGuiaRemision);
                            db.ExecuteNonQuery(cmdDetalle,transaction);
                            
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }
                    
                }
            }
            return idGuiaRemision;
        }

        public void Modificar(GuiaRemision guiaRemision)
        {
            using (DbConnection conection = db.CreateConnection())
            {
                conection.Open();
                using (DbTransaction transaction = conection.BeginTransaction())
                {
                    try
                    {
                        DbCommand cmdGuiaRemision = db.GetStoredProcCommand("UpdateGuiaRemision");

                        db.AddInParameter(cmdGuiaRemision, "@origen", DbType.String, guiaRemision.Origen);
                        db.AddInParameter(cmdGuiaRemision, "@destino", DbType.String, guiaRemision.Destino);
                        db.AddInParameter(cmdGuiaRemision, "@motivo", DbType.String, guiaRemision.Motivo);
                        db.AddInParameter(cmdGuiaRemision, "@transporte", DbType.String, guiaRemision.Transporte);
                        db.AddInParameter(cmdGuiaRemision, "@descripcion", DbType.String, guiaRemision.Descripcion);
                        db.AddInParameter(cmdGuiaRemision, "@idEmpleado", DbType.Int32, guiaRemision.Empleados.IdEmpleado);
                        db.AddInParameter(cmdGuiaRemision, "@fechaEmision", DbType.DateTime, guiaRemision.Fecha);
                        db.AddInParameter(cmdGuiaRemision, "@fechaTranslado", DbType.DateTime, guiaRemision.FechaTranslado);
                        db.AddInParameter(cmdGuiaRemision, "@idDocumento", DbType.Int32, guiaRemision.IdDocumento);
                        db.AddInParameter(cmdGuiaRemision, "@tipoGuia", DbType.Int32, guiaRemision.TipoGuia);
                        db.AddInParameter(cmdGuiaRemision, "@idguiaRemision", DbType.Int32,guiaRemision.IdGuiaRemision);
                        db.ExecuteNonQuery(cmdGuiaRemision, transaction);

                        DbCommand cmdDeleteDetalle =db.GetStoredProcCommand("DeleteDetalleGuiaRemisionByGuiaRemision");

                        db.AddInParameter(cmdDeleteDetalle,"@idGuiaRemision", DbType.Int32,guiaRemision.IdGuiaRemision);
                        db.ExecuteNonQuery(cmdDeleteDetalle,transaction);

                        DbCommand cmdDetalle = db.GetStoredProcCommand("InsertDetalleGuiaRemision");
                        db.AddInParameter(cmdDetalle, "@cantidad", DbType.Decimal);
                        db.AddInParameter(cmdDetalle, "@precio", DbType.Currency);
                        db.AddInParameter(cmdDetalle, "@idBien", DbType.Int32);
                        db.AddInParameter(cmdDetalle, "@idGuiaRemision", DbType.Int32);

                        foreach (DetalleGuiaRemision d in guiaRemision.Detalle)
                        {
                            db.SetParameterValue(cmdDetalle,"@cantidad",d.Cantidad);
                            db.SetParameterValue(cmdDetalle,"@precio", d.Precio);
                            db.SetParameterValue(cmdDetalle,"@idBien",d.Bien.IdBien);
                            db.SetParameterValue(cmdDetalle,"@idGuiaRemision",guiaRemision.IdGuiaRemision);
                            db.ExecuteNonQuery(cmdDetalle,transaction);
                           
                        }

                        transaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw ex;
                    }

                }
            }
        }

        public void Eliminar(int idGuiaRemision)
        {
            DbCommand command = db.GetStoredProcCommand("DeleteGuiaRemision");
            db.AddInParameter(command, "idGuiaRemision", DbType.Int32, idGuiaRemision);
            db.ExecuteNonQuery(command);
        }

        public List<int> ListarIDsPorTipo(int tipo)
        {
            List<int> documentoID = new List<int>();
            DbCommand command = db.GetStoredProcCommand("GetIDDocumentoByTipoGuia");
            db.AddInParameter(command, "tipo", DbType.Int32,tipo);
            IDataReader dr = db.ExecuteReader(command);
            while (dr.Read())
            {

                documentoID.Add(dr.GetInt32(dr.GetOrdinal("IdDocumento")));
            };
            return documentoID;
        }
    }
}
